/**
 * 
 */
package cvcg.dao;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanConstructorResultTransformer;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateSystemException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import cvcg.dto.GameSessionVoteDTO;
import cvcg.model.Game;
import cvcg.model.GameSession;
import cvcg.model.Player;
import cvcg.model.Score;

/**
 * @author jaracje
 * 
 */
@Transactional(propagation=Propagation.REQUIRED)
public class BaseDAOImpl extends HibernateDaoSupport implements BaseDAO {
	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("rawtypes")
	@Transactional(propagation=Propagation.REQUIRED)
	public List getObjects(Class clazz) {
		return getHibernateTemplate().loadAll(clazz);
	}

	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("rawtypes")
	@Transactional(propagation=Propagation.REQUIRED)
	public Object getObject(final Class clazz, final Serializable id) {
		return getHibernateTemplate().get(clazz, id);
	}

	/**
	 * {@inheritDoc}
	 */
	@Transactional(propagation=Propagation.REQUIRED)
	public void saveObject(Object o) {
		getHibernateTemplate().saveOrUpdate(o);
	}

	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("rawtypes")
	@Transactional(propagation=Propagation.REQUIRED)
	public void saveAll(Collection c) {
		try {
			getHibernateTemplate().saveOrUpdateAll(c);

		} catch (HibernateSystemException e) {
			logger.error(e.toString());
			throw e;

		}
	}

	/**
	 * removeObject
	 * 
	 * @param clazz
	 *            Class
	 * @param id
	 *            Serializable
	 */
	@SuppressWarnings("rawtypes")	
	@Transactional(propagation=Propagation.REQUIRED)
	public void removeObject(Class clazz, Serializable id) {
		getHibernateTemplate().delete(getObject(clazz, id));
	}

	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("rawtypes")	
	@Transactional(propagation=Propagation.REQUIRED)
	public void removeAllObjects(Collection entities) {
		getHibernateTemplate().deleteAll(entities);

	}

	@SuppressWarnings("unchecked")
	@Override
	public Player getPlayerByName(String playerName) {
		List<Player> list =  getHibernateTemplate().find("from Player p where p.name='" + playerName + "'");
		if(!list.isEmpty())
		{
			return list.get(0);
		}
		return null;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<GameSessionVoteDTO> getGameSessionVotes(final Long gameSessionId) 
	{
		List<GameSessionVoteDTO> votes = (List) this.getHibernateTemplate().execute(new HibernateCallback()
		{
			public Object doInHibernate(Session session) throws HibernateException
			{
				String queryString="select " 
						+"gm.game_p as gameId, "
						+"gm.name as gameName, "
						+"gm.minPlayer as minPlayer, "
						+"gm.maxPlayer as maxPlayer, "
						+"gm.duration as duration, "
						+"count(gv.vote_p) as votes "
						+"from "
						+"GAME gm, "
						+"GAMESESSION gs, "
						+"VOTE gv " 
						+"where gm.game_p = gv .game_f "
						+"and gv .gamesession_f  = gs.gamesession_p "
						+"and gs.gamesession_p = :gameSessionId "
						+"Group By gm.game_p ";				
				SQLQuery sqlQuery = session.createSQLQuery(queryString);
				
				sqlQuery.setParameter("gameSessionId", gameSessionId);				
				try
				{
					sqlQuery.setResultTransformer(new AliasToBeanConstructorResultTransformer(GameSessionVoteDTO.class.getConstructor(new Class[]{BigInteger.class,String.class,Integer.class,Integer.class,Integer.class,BigInteger.class})));
				}
				catch (SecurityException e)
				{
					logger.error(e);
				}
				catch (NoSuchMethodException e)
				{
					logger.error(e);
				}				
				List resultList = sqlQuery.list();
				
				return resultList;
			}
		});
		return votes;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public GameSession getNextGameSession()
	{
		List<GameSession> gameSessionList = (List) this.getHibernateTemplate().execute(new HibernateCallback()
		{
			public Object doInHibernate(Session session) throws HibernateException
			{
				String queryString="from GameSession gs where gs.date >= current_date() order by gs.date"; 
				Query query = session.createQuery(queryString);				
				List resultList = query.list();
				
				return resultList;
			}
		});
		GameSession nextGameSession=null;
		if (!gameSessionList.isEmpty()) 
		{
			nextGameSession = gameSessionList.get(0);
		}
		return nextGameSession;
	}

	public void flushSession()
	{
		this.getSession().flush();
	}	

	@SuppressWarnings("unchecked")
	public List<GameSession> getGameSessionsByStatus(String status) 
	{
		List<GameSession> list =  getHibernateTemplate().find("from GameSession gs where gs.status='" + status + "'");
		return list;
		
	}
	
	@SuppressWarnings("unchecked")
	public List<Score> getScoreByPlayerName(String playerName) 
	{
		List<Score> list = new ArrayList<Score>();
		Player player = getPlayerByName(playerName);
		if (player!=null) 
		{
			list =  getHibernateTemplate().find("from Score sc where sc.player.id = " +player.getId());
		}
		
		return list;
		
	}

	@SuppressWarnings("unchecked")
	public Score getScoreById(Long id) 
	{
		Score score=null;
		List<Score> list = new ArrayList<Score>();
		list =  getHibernateTemplate().find("from Score sc where sc.id = " +id);
		if (!list.isEmpty()) 
		{
			score = list.get(0);
		}
		return score;
		
	}

	
	@SuppressWarnings("unchecked")
	public List<Game> getAllGamesWithoutScore() 
	{
		List<Game> list = new ArrayList<Game>();
		
		list =  getHibernateTemplate().find("from Game g where not exists (from Score sc inner join sc.game g2 where g2.id = g.id)");				
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<Game> getAllGamesOrderedByName() 
	{
		List<Game> list = new ArrayList<Game>();
		
		list =  getHibernateTemplate().find("select distinct g from Game g order by g.name");				
		return list;
	}

}
